package users;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;



public class Clerk {

	private Connection con;
	
	
	
	
	
	private int totalPrice =0;
	private int receiptID;
	
	private String[] finalItemList = new String[30];
	private String[] finalItemListreturn = new String[30];
	private int[] finalItemPrices = new int[20];
	
	private String[] finalText = new String[30];
	//store information the quantity of items be purchased
	
	private int[] quantityItem =  new int[20];
	
	public Clerk(Connection con)
	{
		this.con = con;
		
	}
	public void searchingPrices(String[] text,  String cardNumber, String expireDate, String storeName) throws SQLException
	{
		// for debugging purpose. i am going to put some values into a Item table
		totalPrice=0;
		
		receiptID = this.getRID();
		//con is Connection object, retrieve tables' item name and prices
		Statement stm1 = con.createStatement();
		
		String itemNames = "";
		// for now initialize size 30 but it would be a good idea to have size as table's number of row
		int[] itemPrices = new int[30];
		// result set should contain information of item names and prices. 
		
		ResultSet rs = stm1.executeQuery("Select upc,title, sellPrice FROM Item");
		
		//count number for itemPrices array
		int i = 0;
		
		// final item's list names 
		//String[] finalItemList = new String[30];
		//int[] finalItemPrices = new int[30];
		
		while(rs.next())
		{
			
			itemNames += rs.getString("upc");
			finalItemList[i] = rs.getString("title");
			itemPrices[i] = rs.getInt("sellPrice");
			
			i++;
			itemNames += " ,";
		}
		
	
		// check whether the upc are matched with result sets
		
		// split itemsNames into array
		
		String[] itemNamesArray = itemNames.split(", *");
		
		int countFinal = 0;
		// get quantity information
		String eachTextFinal;
		
		//initialize finalText 
		for (int c = 0; c<finalText.length; c++)
		{
			finalText[c] = "";
		}
		//this for loop set up two arrays that have customer's purchase Item with associated with quantity
		for(int c= 0; c<text.length; c++)
		{
			eachTextFinal = text[c];
			for (int d=0; d<text.length; d++)
			{
		
				if(finalText[d].equals(eachTextFinal))
				{
					quantityItem[d] +=1;
					break;
				}
				else
				{
					finalText[countFinal] = eachTextFinal;
					quantityItem[countFinal]= 1;
					countFinal++;
					break;
				}
				
			}
			
		
		}
		System.out.println(quantityItem[0]);
		
		// this for loop will setup the array for SQL query
		for (int c = 0; c<finalText.length; c++)
		{
			String eachText = finalText[c];
			for (int d = 0; d< itemNamesArray.length; d++)
			{
				String eachItemnames = itemNamesArray[d].substring(0, itemNamesArray[d].length()-1);
				
				
				if (eachText.equals(eachItemnames))
				{	
					
					finalItemPrices[c] = itemPrices[d];
					totalPrice += (itemPrices[d] * quantityItem[c]);
			
					finalItemListreturn[c] = finalItemList[d];
					
					
					
					
				}
			}
		}
			
		for (int c=0; c<finalItemList.length; c++)
		{	
			if(finalItemList[c] != null)				
			System.out.print("Item name : " + finalItemList[c] + "," + "Item Price : " + finalItemPrices[c]);
			break;
		}
		
		
		stm1.close();
		
		
		int newStock =0;
		System.out.println(finalText[0]);
	
		Statement stm2, stm5, stm6;
		// test
		for (int c =0; c<finalText[c].length(); c++)
		{
			System.out.println(finalText[c]);
		}
		// this for loop is to query stock table
		for(int c =0; c< text.length; c++)
		{
		int oldStock =0;
		stm2 = con.createStatement();
		stm5 = con.createStatement();
		
		//query the old stock from the Stored Table
		if(finalText[c] != "")
		{
		String update = "SELECT stock FROM Stored" + " WHERE name = '" + storeName + "'" ; //+ "' AND upc =" + finalText[c];
		System.out.println(update);
		ResultSet rs2 = stm2.executeQuery(update);
		while(rs2.next())
		{
		
			oldStock = rs2.getInt(1);
			
			System.out.println(oldStock);
		}
		stm2.close();
		// calculate newstock by discrenting with oldstock 
		newStock = oldStock - quantityItem[c];
		
		stm5=con.createStatement();
		{
			String update2 = "UPDATE Stored SET stock =  " +  newStock + " WHERE name = '" + storeName + "'";

			System.out.println(update2);
			stm5.executeUpdate (update2);
			
		}
	
		stm5.close();
		}
		}
		
		// this is today's Date
		Date today = new Date(System.currentTimeMillis());
		Statement stm4 = con.createStatement();
		
			
		String update3;
		if (cardNumber.isEmpty())
		{
			update3 = "Insert into Purchase Values (" +receiptID +", +TO_DATE('" +today +"', 'YYYY-MM-DD'), NULL, NULL,NULL, NULL, NULL,  +TO_DATE('" +today +"', 'YYYY-MM-DD'))";
		}
		else{
			
		
		 update3 = "Insert into Purchase Values (" + receiptID + ", +TO_DATE('" +today +"', 'YYYY-MM-DD'), NULL, NULL," + cardNumber + ",+TO_DATE('" +expireDate +"', 'YYYY-MM-DD'), +TO_DATE('" +today +"', 'YYYY-MM-DD'), +TO_DATE('" +today +"', 'YYYY-MM-DD'))";
		
		}
		
		
			System.out.println (update3);
			stm4.executeUpdate (update3);
		
		stm4.close();
		
		// update 
		//
		
	}
	
	public void insertIntoPurchaseItem() throws SQLException
	{
		Statement stm1 = con.createStatement();
		for (int c =0; c<finalText[c].length(); c++)
		{
			System.out.println(finalText[c]);
			if(finalText[c] != "")
			{
				
			stm1.executeUpdate("INSERT INTO PurchaseItem (receiptID, upc, quantity) " + 
				"VALUES (" +receiptID+ ", '" + finalText[c]+ "', " +quantityItem[c]+ ")");
			
			}
			
		}
		stm1.close();
	}
	public int[] queryReceiptint()
	{
		int[] receiptInfo= new int[2]; 
		
		receiptInfo[0] = receiptID;
		receiptInfo[1] = totalPrice;
		
		return receiptInfo;
		
	}
	public int[] queryQuantity()
	{
		return quantityItem;
	}
	public String[] queryReceiptstring()
	{
		String[] receiptInfo = new String[30];
		
		for (int i=0; i< finalItemListreturn.length; i++ )
		{
			if(finalItemListreturn[i] != null)
			{
			receiptInfo[i] = finalItemListreturn[i];
			receiptInfo[i] += " " ;
			receiptInfo[i] += finalItemPrices[i];
			}
		}
			
		return receiptInfo;
	}
	
	/**
	 * @param receiptID receiptID
	 * @param upc upc
	 * @param quantity quantity
	 * @return 0 if it complete successfully, 1 if upc not exist or quantity too much, 2 if past the return day.
	 * @throws SQLException
	 */
	public int checkReturn(String receiptID, String upc, int quantity, String storeName) throws SQLException 	{
		Statement stmt = con.createStatement();
		
		
		
		ResultSet result = stmt.executeQuery(
				"SELECT i.upc, i.quantity, p.purchaseDate, p.name " +
				"FROM Purchase p, PurchaseItem i " +
				"WHERE i.receiptID = p.receiptID AND i.receiptID = "+receiptID);
		
		
		boolean exist = false;
		Date purchaseDate = null;
		String name = "";
				
		// Check if the upc is in the order, and the quantity ordered.
		while(result.next()){
			
			if (upc.equals(result.getObject("upc")) && quantity 
					<= Integer.parseInt(result.getString("quantity").toString())){
				exist = true;
				purchaseDate = result.getDate("purchaseDate");
				if(!(result.getObject("name") == null))
					name = result.getObject("name").toString();
				break;
			}
		}
		
		// If the UPC doesn't exist, then exit
		if (exist == false){
			stmt.close();
			return 1;
		}
		
        Calendar currentDate = Calendar.getInstance();
        currentDate.add(Calendar.DATE, -15);
        
        java.util.Date bestBeforeDate = currentDate.getTime();
        
        if(bestBeforeDate.after(purchaseDate)){
    		stmt.close();
        	return 2;
        }
        
        Date todayDate = new Date(System.currentTimeMillis());
        int retID = getRetID();
        
        stmt.executeUpdate("INSERT into Return " +
        		"(retid, returnDate, receiptID, name) VALUES " +
        		"('"+retID+"', +TO_DATE('"+todayDate+"', 'YYYY-MM-DD'), '"+receiptID+"', '"+name+"')");

        stmt.executeUpdate("INSERT into ReturnItem " +
        		"(retid, upc, quantity) VALUES " +
        		"("+retID+", '"+upc+"', "+quantity+")");
        
        // Update the stock in the store.
        result = stmt.executeQuery("SELECT stock FROM Stored WHERE upc = '"+upc+"' AND " +
        		"name = '"+storeName+"'");
        int newStock=0;
        if (result.next()){
        	newStock = result.getInt("stock")+quantity;
        }
        stmt.executeUpdate("UPDATE Stored SET stock = "+newStock+" WHERE upc = '"+upc+"' AND " +
        		"name = '"+storeName+"'");
        
        stmt.close();
		
		return 0;
	}
	
	public int getRetID() {
		int retID = 0;
		
		// sql statement
		ResultSet result = null;
		Statement stmt1;
		try {
			stmt1 = con.createStatement();
			result = stmt1.executeQuery("Select MAX(retid) FROM Return");
			
			if(result.next()) {
				retID = result.getInt(1);
			}
		} catch (SQLException e) {
			System.out.println("Could not get maximum return id");
		}
		return retID + 1;
	}
	// get the receipt ID needed for a purchase
	public int getRID() {
		int rid = 0;
		
		// for debugging purposes
		System.out.println("Select MAX(receiptID) FROM Purchase");
		
		// sql statement
		ResultSet result = null;
		Statement stmt1;
		try {
			stmt1 = con.createStatement();
			result = stmt1.executeQuery("Select MAX(receiptID) FROM Purchase");
			
			if(result.next()) {
				rid = result.getInt(1);
			}
			
			stmt1.close();
		} catch (SQLException e) {
			System.out.println("Could not get maximum receipt id");
		}
		
		System.out.println("new rid number: " + (rid + 1));
		return rid + 1;
	}
}
